﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using WeifenLuo.WinFormsUI.Docking;
using System.IO;
using SN.Models;
using SN.Data;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;

namespace SN.Report
{
    public partial class FormTemp : DockContent
    {
        public int startCol = 0, startRow = 0, endCol = 0, endRow = 0;
        public int _x = 0, _y = 0;//当前单元格偏移量
        public string AutoAddFormula = "";//自动填充公式的公式

        private SN.Models.T_Report report;

        public SN.Models.T_Report Report
        {
            get { return report; }
            set { report = value; }
        }

        public BatchInputFormulaDlg Formula; //指标公式
        public JYFormulaDlg YJFormula; //校验公式 

        public FormAutoAddFormula FormAutoAddFormula;


        public Boolean isEdit; //是否编辑

        public FormTemp()
        {
            InitializeComponent();
            //为控件添加双击事件，当双击后，将单元格数据自动回填到公式编辑器中。
            this.cellControl1.axCell.MouseDClick += new AxCELL50Lib._DCell2000Events_MouseDClickEventHandler(axCell_MouseDClick);
        }

        void axCell_MouseDClick(object sender, AxCELL50Lib._DCell2000Events_MouseDClickEvent e)
        {
            string sFormula = string.Empty;
            int iCol = 0;
            int iRow = 0;
            isEdit = true; //修改
            iCol = cellControl1.axCell.GetCurrentCol();
            iRow = cellControl1.axCell.GetCurrentRow();
            if (Formula != null)
            {
                if (!string.IsNullOrWhiteSpace(Formula.Text))
                {
                    sFormula = Formula.richTextBox1.Text + this.cellControl1.axCell.GetCellString(iCol, iRow, cellControl1.axCell.GetCurSheet());
                    Formula.richTextBox1.Text = sFormula;
                    cellControl1.axCell.SetCellTip(startCol, startRow, cellControl1.axCell.GetCurSheet(), sFormula); //标注
                }
            }
            else if (YJFormula != null)
            {
                //校验公式
                if (!string.IsNullOrWhiteSpace(YJFormula.Text))
                {
                    sFormula = YJFormula.richTextBox1.Text + this.cellControl1.axCell.GetCellString(iCol, iRow, cellControl1.axCell.GetCurSheet());
                    YJFormula.richTextBox1.Text = sFormula;
                    //cellControl1.axCell.SetCellBackColor(iCol, iRow, cellControl1.axCell.GetCurSheet(), 11);
                    //cellControl1.axCell.SetCellTip(startCol, startRow, cellControl1.axCell.GetCurSheet(), sFormula); //标注
                }
            }
        }

        public static uint ParseRGB(Color color)
        {
            return (uint)(((uint)color.B << 16) | (ushort)(((ushort)color.G << 8) | color.R));
        }

        public void DeleteReport(ref string Msg)
        {
            using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
            {
                try
                {
                    if (Report == null) throw new Exception("报表数据不存在!请选择要删除的报表");
                    //判断是否存在指标数据
                    int iCount = trans.Count<T_ZBSJ>(T_ZBSJ._.ZBSJ_ReportID == report.Report_ID.ToString());
                    if (iCount > 0) throw new Exception("已存在指标数据，不允许删除!");

                    trans.Delete<SN.Models.T_Report>(T_Report._.Report_ID == Report.Report_ID);
                    trans.Delete<SN.Models.T_ReportFormat>(T_ReportFormat._.ReportFormat_ReportID == report.Report_ID);
                    trans.Delete<SN.Models.T_ZBZD>(T_ZBZD._.ZBZD_ReportID == report.Report_ID);

                    //
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    Msg = ex.Message;
                }

            }
            return;
        }

        public string RenameReport(string newName)
        {
            string Msg = string.Empty;

            if (string.IsNullOrEmpty(newName)) return "报表名称不能为空!";

            using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
            {
                try
                {

                    trans.Update<T_Report>(T_Report._.Report_Name, newName, T_Report._.Report_ID == Report.Report_ID);

                    //提交数据
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    Msg = ex.Message;
                }
            }
            return Msg;
        }

        public string GetHtmlClr(int iColor)
        {
            Color aColor = Color.FromArgb(0, (int)iColor >> 16 & 0xff, (int)iColor >> 8 & 0xff, (int)iColor & 0xff);
            return ColorTranslator.ToHtml(aColor);
        }

        /// <summary>
        /// 指标化
        /// </summary>
        /// <returns></returns>
        public string GenerateZBH()
        {
            //数据化：数据化过的单元格可以存储数据，
            //没有数据化过的单元格存储的数据将不被保存到数据库中。
            //在自由报表定义时需要对每个单元格进行数据化，
            //数据化后每个单元格都有一个唯一的编号，
            //输入数据后根据唯一的编号存放到数据库中，以便其它报表引用，或以后便于查询。
            string Msg = string.Empty;
            string strZbbh = string.Empty;
            if (MessageBox.Show("要进行指标化吗?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) != System.Windows.Forms.DialogResult.Yes) return "";
            //如果有指标数据，则不允许再进行指标化，此处不能有这样的判断，原因，当一个表制定完成后，再要加指标，如果进行此判断，刚不能新加指标
            //int iCount = SN.Data.DBUtility.DB.Count<T_ZBSJ>(T_ZBSJ._.ZBSJ_ReportID == report.Report_ID);
            //if (iCount > 0)
            //{
            //    MessageBox.Show("已存在指标数据，不允许指标化!");
            //    return "";
            //}
            //获取当前的选中单元格，将值赋给startCol，startRow，endCol，endRow
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            //开启事务
            try
            {
                SN.Services.SNService.Hourglass(true); //设置图标为等待状态
                isEdit = true;
                //trans.Delete<T_ZBZD>(T_ZBZD._.ZBZD_ReportID == report.Report_ID);
                for (int i = startCol; i <= endCol; i++)
                {
                    for (int j = startRow; j <= endRow; j++)
                    {
                        SN.Services.SNService.Hourglass(true);
                        //
                        strZbbh = SN.Services.SNService.GetLSBH<T_ZBZD>();

                        string CurrCellString = this.cellControl1.axCell.GetCellString(i, j, this.cellControl1.axCell.GetCurSheet());

                        //如果当前单元格的内容为空，方可进行指标化。
                        if (string.IsNullOrEmpty(CurrCellString))
                        {
                            this.cellControl1.axCell.SetCellString(i, j, this.cellControl1.axCell.GetCurSheet(), "[Calc]" + strZbbh);
                            this.cellControl1.axCell.SetCellInput(i, j, this.cellControl1.axCell.GetCurSheet(), 5);//设置为只读
                            //设置背景颜色
                            int myColor = (int)ParseRGB(Color.BurlyWood);
                            this.cellControl1.axCell.SetCellBackColor(i, j, this.cellControl1.axCell.GetCurSheet(), this.cellControl1.axCell.FindColorIndex(myColor, 1));
                        }
                        //指标编号生成规则：[calc]001001 行列
                    }
                }
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
            }
            finally
            {
                SN.Services.SNService.Hourglass(false);
            }

            return Msg;
        }

        /// <summary>
        /// 取消指标化
        /// </summary>
        /// <returns></returns>
        public string UnGenerateZBH()
        {
            string Msg = string.Empty;
            string strZbbh = string.Empty;
            if (MessageBox.Show("要取消指标化吗?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) != System.Windows.Forms.DialogResult.Yes) return "";

            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);

            try
            {
                SN.Services.SNService.Hourglass(true); //设置图标为等待状态
                for (int i = startCol; i <= endCol; i++)
                {
                    for (int j = startRow; j <= endRow; j++)
                    {
                        SN.Services.SNService.Hourglass(true);

                        string CurrCellString = this.cellControl1.axCell.GetCellString(i, j, this.cellControl1.axCell.GetCurSheet());
                        //如果当前单元格的内容是以[Calc]开头，才能取消指标化
                        if (!string.IsNullOrEmpty(CurrCellString) && CurrCellString.IndexOf("[Calc]")>= 0)
                        {
                            this.cellControl1.axCell.SetCellString(i, j, this.cellControl1.axCell.GetCurSheet(), "");//将单元格内容置空
                            this.cellControl1.axCell.SetCellTip(i, j, this.cellControl1.axCell.GetCurSheet(),"");//将单元格公式置空
                            //校验公式是直接写进数据库的，所以此处不用删除。因为取消指标化后，需要重新生成指标字典，校验公式将自动删除。


                            this.cellControl1.axCell.SetCellInput(i, j, this.cellControl1.axCell.GetCurSheet(), 0);//设置为只读
                            //设置背景颜色
                            int myColor = (int)ParseRGB(Color.White);
                            this.cellControl1.axCell.SetCellBackColor(i, j, this.cellControl1.axCell.GetCurSheet(), this.cellControl1.axCell.FindColorIndex(myColor, 1));
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
            }
            finally
            {
                SN.Services.SNService.Hourglass(false);
            }

            return Msg;
        }

        #region 生成指标字典
        /// <summary>
        /// 根据指标所在行与列，生成指标字典、做统计分析用
        /// </summary>
        /// <returns></returns>
        public string GenerateZBZD()
        {
            //数据化：数据化过的单元格可以存储数据，
            //没有数据化过的单元格存储的数据将不被保存到数据库中。
            //在自由报表定义时需要对每个单元格进行数据化，
            //数据化后每个单元格都有一个唯一的编号，
            //输入数据后根据唯一的编号存放到数据库中，以便其它报表引用，或以后便于查询。
            string Msg = string.Empty;
            string strZbbh = string.Empty;

            if ((string.IsNullOrEmpty(report.Report_IndexCol)) || string.IsNullOrEmpty(report.Reprot_DimensionRow))
            {
                return "该报表还没有设置报表属性，请设置属性后再进行操作!";
            }
            if (MessageBox.Show("确认要生成指标字典吗?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) != System.Windows.Forms.DialogResult.Yes) return "";

            isEdit = true;
            //开启事务
            using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
            {
                try
                {
                    SN.Services.SNService.Hourglass(true); //设置图标为等待状态
                    trans.Delete<T_ZBZD>(T_ZBZD._.ZBZD_ReportID == report.Report_ID);
                    for (int i = 1; i <= this.cellControl1.axCell.GetCols(cellControl1.axCell.GetCurSheet()); i++)
                    {
                        for (int j = 1; j <= this.cellControl1.axCell.GetRows(cellControl1.axCell.GetCurSheet()); j++)
                        {
                            SN.Services.SNService.Hourglass(true);

                            string sText = this.cellControl1.axCell.GetCellString(i, j, this.cellControl1.axCell.GetCurSheet());
                            string sFormula = this.cellControl1.axCell.GetCellTip(i, j, this.cellControl1.axCell.GetCurSheet());//获取公式
                            sText = sText.Replace("\r", "").Replace("\n", "").Replace(" ", "");
                            if (sText.IndexOf("[Calc]") >= 0)
                            {
                                //存指标
                                T_ZBZD ZBZD = new T_ZBZD();
                                ZBZD.ZBZD_ReportID = Report.Report_ID;
                                ZBZD.ZBZD_ZBBH = sText.Replace("[Calc]", "");
                                ZBZD.ZBZD_Remark = j.ToString() + "," + i.ToString();//坐标位置 

                                ZBZD.ZBZD_Index = this.cellControl1.axCell.GetCellString(Convert.ToInt16(report.Report_IndexCol), j, this.cellControl1.axCell.GetCurSheet()).Trim(); //指标
                                ZBZD.ZBZD_Dimension = this.cellControl1.axCell.GetCellString(i, Convert.ToInt16(report.Reprot_DimensionRow), this.cellControl1.axCell.GetCurSheet()).Trim().Replace("\r\n", "").Replace(" ", ""); //维度
                                ZBZD.ZBZD_Order = j; //取指标所在列

                                ZBZD.ZBZD_CreateDate = DateTime.Now.ToString("yyyyMMdd");
                                ZBZD.ZBZD_CreateIP = "";
                                ZBZD.ZBZD_CreateUserID = SN.Services.SNService.User.User_ID.ToString();
                                if (!string.IsNullOrEmpty(sFormula)) ZBZD.ZBZD_Formula = sFormula.Replace("指标公式:", "");//如果公式有内容，则赋值，否则保持数据库中公式字段为NULL值。
                                

                                ZBZD.Detach(); //新增
                                //保存数据
                                trans.Save<T_ZBZD>(ZBZD);

                            }
                            //指标编号生成规则：[calc]001001 行列
                        }
                    }
                    //提交事务
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    Msg = ex.Message;
                }
                finally
                {
                    SN.Services.SNService.Hourglass(false);
                }
            }

            return Msg;

        }
        #endregion

        #region 生成Web格式
        /// <summary>
        /// 生成Web格式
        /// </summary>
        /// <returns></returns>
        public string GenerateWebFormat()
        {
            string Msg = string.Empty;
            if (MessageBox.Show("确认要进行Web化吗?", "提示!", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1) != System.Windows.Forms.DialogResult.Yes) return "";

            isEdit = true;
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            decimal totalcell = (endRow - startRow + 1) * (endCol - startCol + 1);
            decimal nowcell = 0;
            using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
            {
                try
                {
                    //保存Reprot属性行高与列宽
                    string sRowHeight = "";
                    string sColWidth = "";
                    for (int j = startCol; j < endCol + 1; j++) //循环列宽
                    {
                        sColWidth = sColWidth + "," + this.cellControl1.axCell.GetColWidth(1, j, this.cellControl1.axCell.GetCurSheet()).ToString(); //列宽
                    }
                    SN.Services.SNService.Hourglass(true); //设置图标为等待状态
                    //先删除本报表已生成的web格式数据
                    trans.Delete<T_ReportContents>(T_ReportContents._.CONTENTS_REPORTID == Report.Report_ID);
                    for (int i = startRow; i < endRow + 1; i++)
                    {
                        sRowHeight = sRowHeight + "," + this.cellControl1.axCell.GetRowHeight(1, i, this.cellControl1.axCell.GetCurSheet()).ToString();

                        for (int j = startCol; j < endCol + 1; j++)
                        {
                            this.cellControl1.axCell.SelectRange(j, i, j, i);
                            int scol = 0, ecol = 0, srow = 0, erow = 0;
                            this.cellControl1.axCell.GetSelectRange(ref scol, ref srow, ref ecol, ref erow);

                            int rowspan = erow - srow + 1;//合并行
                            int colspan = ecol - scol + 1;//合并列
                            if (i == srow && j == scol)
                            {
                                //开始写数据库信息
                                SN.Models.T_ReportContents ReportContents = new T_ReportContents();
                                ReportContents.CONTENTS_REPORTID = Report.Report_ID;
                                string text = this.cellControl1.axCell.GetCellString(scol, srow, cellControl1.axCell.GetCurSheet());
                                text = text.Replace("\r\n", "<br/>"); //替换web中的换行符
                                text = text.Replace(System.Text.Encoding.ASCII.GetString(new Byte[1] { 32 }), "&nbsp;");//替换英文空格

                                // ss = ss.Replace(" ", "&nbsp;");
                                int a = text.Length;
                                text = text.TrimStart(' '); //取字符前的空格数，即缩进数
                                int b = text.Length;
                                if (text.IndexOf("[Calc]") >= 0 && string.IsNullOrEmpty(cellControl1.axCell.GetCellTip(scol, srow, cellControl1.axCell.GetCurSheet())))
                                {
                                    //SetCellTip
                                    ReportContents.CONTENTS_IsEdit = "1";
                                }
                                else
                                {
                                    ReportContents.CONTENTS_IsEdit = "0";
                                }
                                //有公式的情况下
                                ReportContents.CONTENTS_Formula = cellControl1.axCell.GetCellTip(scol, srow, cellControl1.axCell.GetCurSheet()).Replace("指标公式:", "");
                                ReportContents.CONTENTS_TEXT = text; //内容
                                ReportContents.CONTENTS_Indentation = (a - b).ToString(); //缩进
                                ReportContents.CONTENTS_ROW = srow;
                                ReportContents.CONTENTS_ROWSPAN = rowspan;
                                ReportContents.CONTENTS_COL = scol;
                                ReportContents.CONTENTS_COLSPAN = colspan;
                                //
                                int FontStyle = this.cellControl1.axCell.GetCellFontStyle(scol, srow, this.cellControl1.axCell.GetCurSheet());
                                switch (FontStyle)
                                {
                                    case 2: //粗体
                                        ReportContents.CONTENTS_FontBold = "font-weight:bold";
                                        break;
                                    case 4://斜体 
                                        ReportContents.CONTENTS_FontItalic = "font-style:italic";
                                        break;
                                    case 8: //下滑线
                                        ReportContents.CONTENTS_FontUnderline = "text-decoration:underline";
                                        break;
                                    case 16://删除线
                                        ReportContents.CONTENTS_FontStrikeout = "text-decoration:line-through";
                                        break;
                                    default:
                                        break;
                                }
                                int FontAlign = cellControl1.axCell.GetCellAlign(scol, srow, cellControl1.axCell.GetCurSheet());
                                switch (FontAlign)
                                {
                                    case 1://左对齐
                                        ReportContents.CONTENTS_H = "text-align:left";
                                        ReportContents.CONTENTS_V = "";
                                        break;
                                    case 2://右对齐
                                        ReportContents.CONTENTS_H = "text-align:right";
                                        ReportContents.CONTENTS_V = "";
                                        break;
                                    case 4: //水平居中
                                        ReportContents.CONTENTS_H = "text-align:center";
                                        ReportContents.CONTENTS_V = "";
                                        break;
                                    case 8://居上对齐
                                        ReportContents.CONTENTS_H = "";
                                        ReportContents.CONTENTS_V = "vertical-align:text-top";
                                        break;
                                    case 16://居下对齐
                                        ReportContents.CONTENTS_H = "";
                                        ReportContents.CONTENTS_V = "vertical-align:text-bottom";
                                        break;
                                    case 32://垂直居中
                                        ReportContents.CONTENTS_H = "";
                                        ReportContents.CONTENTS_V = "vertical-align:text-middle";
                                        break;
                                    case 9:
                                        ReportContents.CONTENTS_H = "text-align:left";
                                        ReportContents.CONTENTS_V = "vertical-align:text-top";
                                        break;
                                    case 17:
                                        ReportContents.CONTENTS_H = "text-align:left";
                                        ReportContents.CONTENTS_V = "vertical-align:text-bottom";
                                        break;
                                    case 33:
                                        ReportContents.CONTENTS_H = "text-align:left";
                                        ReportContents.CONTENTS_V = "vertical-align:text-middle";
                                        break;
                                    case 10:
                                        ReportContents.CONTENTS_H = "text-align:right";
                                        ReportContents.CONTENTS_V = "vertical-align:text-top";
                                        break;
                                    case 12:
                                        ReportContents.CONTENTS_H = "text-align:center";
                                        ReportContents.CONTENTS_V = "vertical-align:text-top";
                                        break;
                                    case 18:
                                        ReportContents.CONTENTS_H = "text-align:right";
                                        ReportContents.CONTENTS_V = "vertical-align:text-bottom";
                                        break;
                                    case 20://水平居中 
                                        ReportContents.CONTENTS_H = "text-align:center";
                                        ReportContents.CONTENTS_V = "vertical-align:text-bottom";
                                        break;
                                    case 34://
                                        ReportContents.CONTENTS_H = "text-align:right";
                                        ReportContents.CONTENTS_V = "vertical-align:text-middle";
                                        break;
                                    case 36://水平居中 加垂直居中
                                        ReportContents.CONTENTS_H = "text-align:center";
                                        ReportContents.CONTENTS_V = "vertical-align:text-middle";
                                        break;
                                    default:
                                        break;
                                }
                                string FontName = cellControl1.axCell.GetFontName(cellControl1.axCell.GetCellFont(scol, srow, cellControl1.axCell.GetCurSheet()));
                                ReportContents.CONTENTS_FontName = FontName; //设置字体

                                float FontSize = cellControl1.axCell.GetCellFontSize(scol, srow, cellControl1.axCell.GetCurSheet());
                                ReportContents.CONTENTS_FontSize = Convert.ToInt16(FontSize); //设置字体大小
                                //边框
                                int iBorder = 0;
                                int iBorderClr = 0;
                                string sBorderStyle = "";
                                string strClr = "";
                                Color c;
                                //左
                                iBorder = cellControl1.axCell.GetCellBorder(scol, srow, cellControl1.axCell.GetCurSheet(), 0); //左
                                iBorderClr = cellControl1.axCell.GetColor(cellControl1.axCell.GetCellBorderClr(scol, srow, cellControl1.axCell.GetCurSheet(), 0)); //左
                                c = ColorTranslator.FromWin32(iBorderClr);
                                strClr = ColorTranslator.ToHtml(c);
                                // strClr = GetHtmlClr(iBorderClr);
                                sBorderStyle = "border-left:" + (iBorder - 1).ToString() + "px solid " + strClr + ";";
                                //上
                                iBorder = cellControl1.axCell.GetCellBorder(scol, srow, cellControl1.axCell.GetCurSheet(), 0); //上
                                iBorderClr = cellControl1.axCell.GetColor(cellControl1.axCell.GetCellBorderClr(scol, srow, cellControl1.axCell.GetCurSheet(), 0)); //上
                                c = ColorTranslator.FromWin32(iBorderClr);
                                strClr = ColorTranslator.ToHtml(c);
                                //strClr = GetHtmlClr(iBorderClr);
                                sBorderStyle += "border-top:" + (iBorder - 1).ToString() + "px solid " + strClr + ";";
                                //右
                                iBorder = cellControl1.axCell.GetCellBorder(scol, srow, cellControl1.axCell.GetCurSheet(), 0); //上
                                iBorderClr = cellControl1.axCell.GetColor(cellControl1.axCell.GetCellBorderClr(scol, srow, cellControl1.axCell.GetCurSheet(), 0)); //上
                                c = ColorTranslator.FromWin32(iBorderClr);
                                strClr = ColorTranslator.ToHtml(c);
                                //strClr = GetHtmlClr(iBorderClr);
                                sBorderStyle += "border-right:" + (iBorder - 1).ToString() + "px solid " + strClr + ";";
                                //下
                                iBorder = cellControl1.axCell.GetCellBorder(scol, srow, cellControl1.axCell.GetCurSheet(), 0); //上
                                iBorderClr = cellControl1.axCell.GetColor(cellControl1.axCell.GetCellBorderClr(scol, srow, cellControl1.axCell.GetCurSheet(), 0)); //上
                                c = ColorTranslator.FromWin32(iBorderClr);
                                strClr = ColorTranslator.ToHtml(c);
                                //strClr = GetHtmlClr(iBorderClr);
                                sBorderStyle += "border-bottom:" + (iBorder - 1).ToString() + "px solid " + strClr + ";";

                                ReportContents.CONTENTS_BorderStyle = sBorderStyle;
                                //添加到数据库
                                //得到单元格格式
                                int iDigital = 0;
                                int iHideZone = 0;
                                int iNumType = this.cellControl1.axCell.GetCellNumType(scol, srow, this.cellControl1.axCell.GetCurSheet());
                                string sFormat = "";
                                //存储时，0 代表字符 1 代表数值
                                switch (iNumType)
                                {
                                    case 0: //缺省
                                    case 7: //文本
                                        sFormat = "0";//字符串
                                        break;
                                    case 1://普通数值 
                                    case 2://货币
                                    case 8://货币大写
                                        iDigital = this.cellControl1.axCell.GetCellDigital(scol, srow, this.cellControl1.axCell.GetCurSheet()); //As Long取得小数位
                                        //iHideZone = this.cellControl1.axCell.GetCellHideZero(scol, srow, this.cellControl1.axCell.GetCurSheet()); //As Long 得到指定单元格是否隐藏 0值(0不隐藏 默认值0)。 
                                        if (iDigital == null) iDigital = 0;
                                        sFormat = string.Format("{0},{1}", iNumType, iDigital);

                                        break;
                                    default:
                                        sFormat = "0"; //按字符串处理
                                        break;
                                }
                                ReportContents.CONTENTS_Format = sFormat;
                                ReportContents.Detach();
                                trans.Save<T_ReportContents>(ReportContents);

                            }
                            j = j + colspan - 1;//如果合并列的话改变列索引
                            nowcell = nowcell + 1 + colspan - 1;
                        }
                    }

                    this.cellControl1.axCell.SelectRange(startCol, startRow, endCol, endRow);
                    //更新Report 行高与列宽 与是否生成Web格式
                    trans.Update<T_Report>(new Field[] { T_Report._.Report_RowHeight, T_Report._.Report_ColWidth, T_Report._.Report_IsWeb },
                                           new object[] { sRowHeight.Substring(1), sColWidth.Substring(1), "1" },
                                           T_Report._.Report_ID == Report.Report_ID);

                    //提交事务
                    trans.Commit();
                    SN.Services.SNService.Hourglass(false); //设置图标为正常
                }
                catch (Exception ex)
                {
                    SN.Services.SNService.Hourglass(false); // 设置图标为正常
                    trans.Rollback();

                    Msg = ex.Message;
                }
            }
            return Msg;
        }
        #endregion

        //写文件
        public string WriteBinaryData()
        {
            if (report == null) throw new Exception("报表参数不能为空!");
            if (this.cellControl1.axCell.GetTotalSheets() > 1) throw new Exception("目前只允许有一个工作页(sheet)");
            string Msg = string.Empty;
            try
            {
                object buffer = null;
                cellControl1.axCell.SaveToBuffer(ref buffer);
                //开始保存数据
                using (SN.Data.DbTrans trans = SN.Data.DBUtility.DB.BeginTrans())
                {
                    try
                    {
                        if (string.IsNullOrEmpty(Report.Report_ID))
                        {
                            //新增
                            Report.Report_ID = SN.Services.SNService.GetLSBH<T_Report>();
                            Report.Report_CreateDate = DateTime.Today.ToString("yyyyMMdd");
                            report.Report_NewDate = DateTime.Today.ToString("yyyyMMdd");
                            report.Report_IsWeb = "0";//是否Web
                            //新增
                            Report.Detach();
                        }
                        else
                        {
                            report.Report_NewDate = DateTime.Today.ToString("yyyyMMdd"); //更新的数据内容
                            //更新
                            Report.Attach();
                        }
                        //更新
                        trans.Save<T_Report>(Report);
                        //先删除T_ReportFormat
                        trans.Delete<T_ReportFormat>(T_ReportFormat._.ReportFormat_ReportID == Report.Report_ID);
                        T_ReportFormat ReportFormat = new T_ReportFormat();
                        ReportFormat.ReportFormat_ReportID = Report.Report_ID;
                        ReportFormat.ReportFormat_Format = (byte[])buffer; //GetBinaryFormatData(buffer);// bt;
                        ReportFormat.Detach();
                        trans.Save<T_ReportFormat>(ReportFormat);
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        Msg = ex.Message;
                    }
                    //更新数据
                    trans.Commit();
                    isEdit = false; //修改编辑状态为未编辑
                }
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
                MessageBox.Show(ex.Message);
            }
            //}
            return Msg;
        }
        //读取文件
        public void ReadBinaryData(string ReportID)
        {
            DataTable dt = SN.Data.DBUtility.DB.From<T_Report>()
                        .InnerJoin<T_ReportFormat>(T_ReportFormat._.ReportFormat_ReportID == T_Report._.Report_ID)
                        .Where(T_Report._.Report_ID == ReportID)
                        .ToDataTable();
            if (dt.Rows.Count <= 0) return;

            byte[] bt = (byte[])dt.Rows[0]["ReportFormat_Format"];

            this.cellControl1.axCell.ReadFromBuffer(bt);
            //string filepath = "C:\\1.cll";//this.cellControl1.GetTempFileName();// cellControl.GetTempFileName(); //随机文件名
            //FileStream filesstream = new FileStream(filepath, FileMode.Create);
            //filesstream.Write(bt, 0, (int)bt.Length);

            ////读取文件
            //this.cellControl1.axCell.OpenFile(filepath, "");
        }

        private void FormTemp_Load(object sender, EventArgs e)
        {
        }

        private void 批量输入公式ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //先判断指标编号是不是已经生成
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            if (startCol != endCol || startRow != endRow)
            {
                MessageBox.Show("请选中一个单元格", "提示!");
                return;
            }
            //判断该指标是不是已经指标化
            string sZbbh = this.cellControl1.axCell.GetCellString(startCol, startRow, this.cellControl1.axCell.GetCurSheet());
            if (sZbbh.IndexOf("[Calc]") == -1)
            {
                MessageBox.Show("还没有指标化，不允许定义公式!");
                return;
            }
            isEdit = true;
            //将公式内容写在单元格的tip中，在生成指标字典时，同时生成公式
            Formula = new BatchInputFormulaDlg();
            Formula.FrmTemp = this;
            Formula.Show();

        }

        private void 指标化ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Msg = string.Empty;
            Msg = GenerateZBH(); //
            if (!string.IsNullOrEmpty(Msg))
            {
                MessageBox.Show(Msg);
                return;
            }
        }

        private void 生成Web格式ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Msg = string.Empty;
            Msg = GenerateWebFormat();
            if (!string.IsNullOrEmpty(Msg))
            {
                MessageBox.Show(Msg);
            }
        }

        private void 生成指标字典ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Msg = string.Empty;
            Msg = GenerateZBZD();
            if (!string.IsNullOrEmpty(Msg))
            {
                MessageBox.Show(Msg);
            }
        }

        private void 报表属性ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            FormAttribute frmAttr = new FormAttribute();
            frmAttr.Report = this.Report;
            frmAttr.ShowDialog();
        }

        private void 剪切ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            this.cellControl1.axCell.CutRange(startCol, startRow, endCol, endRow);
        }

        private void 复制ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            this.cellControl1.axCell.CopyRange(startCol, startRow, endCol, endRow);
        }

        private void 粘贴ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            this.cellControl1.axCell.Paste(startCol, startRow, 0, 1, 0);
        }

        private void FormTemp_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (isEdit)
            {
                DialogResult dr = MessageBox.Show("是否要保存数据？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (dr == DialogResult.Yes)
                {
                    e.Cancel = true;
                    this.WriteBinaryData();
                    return;
                }
                else
                {
                    e.Cancel = false;
                }
            }
        }

        private void 单元格校验公式ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //先判断指标编号是不是已经生成
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            if (startCol != endCol || startRow != endRow)
            {
                MessageBox.Show("请选中一个单元格", "提示!");
                return;
            }
            //判断该指标是不是已经指标化
            string sZbbh = this.cellControl1.axCell.GetCellString(startCol, startRow, this.cellControl1.axCell.GetCurSheet());
            if (sZbbh.IndexOf("[Calc]") == -1)
            {
                MessageBox.Show("还没有指标化，不允许定义公式!");
                return;
            }
            isEdit = true;
            //指标编号 存的没有[Calc]
            sZbbh = sZbbh.Replace("[Calc]", "");
            T_ZBZD ZBZD = SN.Data.DBUtility.DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == report.Report_ID && T_ZBZD._.ZBZD_ZBBH == sZbbh).ToSingle();
            if (ZBZD == null)
            {
                MessageBox.Show("在指标字典中没有找到该报表的指标！");
                return;
            }
            ZBZD.ZBZD_ZBBH = sZbbh;
            //指标字典
            YJFormula = new JYFormulaDlg();
            YJFormula.FrmTemp = this;
            YJFormula.Zbzd = ZBZD;
            //设置 当前选中单元格的x坐标
            YJFormula.currSelectCellX = startCol;
            YJFormula.currSelectCellY = startRow;
            //
            YJFormula.Show();

        }

        private void 上传模版ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            var dialogResult = openFileDialog1.ShowDialog();
            if (dialogResult == System.Windows.Forms.DialogResult.OK)
            {
                T_Report report = DBUtility.DB.Single<T_Report>(T_Report._.Report_ID == this.Report.Report_ID);
                if (report.Report_ExportTemplete != null && report.Report_ExportTemplete.Length > 0)
                {
                    if (MessageBox.Show("系统检测到该报表已存在导出模版，继续操作将会覆盖原模板，是否继续？", "提示", MessageBoxButtons.YesNo,
                          MessageBoxIcon.Question) != System.Windows.Forms.DialogResult.Yes) return;
                }
                string fileName = openFileDialog1.FileName;
                byte[] buffer = null;
                using (System.IO.FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
                {
                    buffer = new byte[fs.Length];
                    fs.Read(buffer, 0, (int)fs.Length);
                }
                DBUtility.DB.Update<T_Report>(T_Report._.Report_ExportTemplete, buffer, T_Report._.Report_ID == this.Report.Report_ID);
                MessageBox.Show("上传成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void 下载模版ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            T_Report report = DBUtility.DB.Single<T_Report>(T_Report._.Report_ID == this.Report.Report_ID);
            if (report.Report_ExportTemplete != null && report.Report_ExportTemplete.Length > 0)
            {
                SaveFileDialog savefile = new SaveFileDialog();
                FileStream fs=null;
                savefile.FileName = report.Report_Name;
                savefile.Filter = "Excel文件|*.xls";
                if (savefile.ShowDialog() == DialogResult.OK)
                {


                    fs = new FileStream(savefile.FileName, FileMode.Create);
                    BinaryWriter bw = new BinaryWriter(fs);//以二进制方式写文件 

                    bw.Write(report.Report_ExportTemplete, 0, report.Report_ExportTemplete.Length);
                    
                    fs.Close();
                    bw.Close();

                }
                else
                {
                    MessageBox.Show("模版下载失败！");
                }

            }
            else
            {
                MessageBox.Show("该报表没有上传导出模板");
            }
        }

        private void 自动生成校验公式ToolStripMenuItem_Click(object sender, EventArgs e)
        {

            FormAutoAddFormula=new FormAutoAddFormula();
            FormAutoAddFormula.FrmTemp = this;
            FormAutoAddFormula.ShowDialog();
            if (string.IsNullOrEmpty(AutoAddFormula))
            {
                MessageBox.Show("应用公式不能为空！");
                return;
            }
            //开始自动填充公式
            this.cellControl1.axCell.GetSelectRange(ref startCol, ref startRow, ref endCol, ref endRow);
            for (int i = startCol; i <= endCol; i++)
            {
                for (int j = startRow; j <= endRow; j++)
                {

                    if (addformula(i, j) == false)
                        break;

                }
            }
        }

        //自动批量添加批量校验公式方法
        public bool addformula(int currSelectCellX, int currSelectCellY)
        {

            string lsFormula = "";
            try
            {
                //判断该指标是不是已经指标化
                string sZbbh = this.cellControl1.axCell.GetCellString(currSelectCellX, currSelectCellY, this.cellControl1.axCell.GetCurSheet());
                if (sZbbh.IndexOf("[Calc]") == -1)
                {
                    MessageBox.Show("还没有指标化，不允许定义公式!");
                    return false;
                }
                isEdit = true;
                //指标编号 存的没有[Calc]
                sZbbh = sZbbh.Replace("[Calc]", "");
                T_ZBZD zbzd = SN.Data.DBUtility.DB.From<T_ZBZD>().Where(T_ZBZD._.ZBZD_ReportID == report.Report_ID && T_ZBZD._.ZBZD_ZBBH == sZbbh).ToSingle();
                if (zbzd == null)
                {
                    MessageBox.Show("在指标字典中没有找到该报表的指标！");
                    return false;
                }
                //zbzd.ZBZD_ZBBH = sZbbh;

                //取得要比较的指标
                string newZbbh = this.cellControl1.axCell.GetCellString(currSelectCellX + _x, currSelectCellY + _y, this.cellControl1.axCell.GetCurSheet());
                lsFormula = AutoAddFormula + newZbbh;

                //判断字符长度不能超过1000
                if (lsFormula.Length > 1000)
                {
                    MessageBox.Show("公式太长啦!", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                if (lsFormula.Length <= 1)
                {
                    MessageBox.Show("公式太短啦!", "提示!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }

                int iSheet = this.cellControl1.axCell.GetCurSheet();
                int myColor = (int)ParseRGB(Color.Yellow); //设置成黄色
                this.cellControl1.axCell.SetCellBackColor(currSelectCellX, currSelectCellY, iSheet, myColor);
                //写开始行 与开始列 （即指标公式所在的行与列)
                //FrmTemp.cellControl1.axCell.SetCellTip(FrmTemp.startCol, FrmTemp.startRow, iSheet, this.richTextBox1.Text);
                //保存数据
                zbzd.ZBZD_JYFormula = lsFormula;
                zbzd.Attach();
                SN.Data.DBUtility.DB.Save<T_ZBZD>(zbzd);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "提示!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            return true;
        }

        private void 取消指标化toolStripMenuItem_Click(object sender, EventArgs e)
        {
            string Msg = string.Empty;
            Msg = UnGenerateZBH(); //
            if (!string.IsNullOrEmpty(Msg))
            {
                MessageBox.Show(Msg);
                return;
            }
        }

       

    }
}
